package com.metagurukul.informer.dao;

import com.metagurukul.informer.utility.ConnectionFactory;
import com.metagurukul.informer.valueObjects.*;
import java.util.*;
import java.sql.*;

/**
 * Class AnswersDAO for interaction with the answers table of the database
 * @author 
 *
 */
public class AnswersDAO
{

	
	/**
	 * ...Method for extracting all the answers posted for a question
	 * @param id question_id
	 * @return list of answers of a question
	 */
    public List<Answers> findById(int id) throws SQLException	{
    	Connection con= ConnectionFactory.getInstance();
    	List<Answers> answers=new ArrayList<Answers>();
   	
    	String sql = "SELECT * FROM answers WHERE question_id="+id;
    	Statement stm = con.createStatement();
    	ResultSet rs=stm.executeQuery(sql); 
    	if(rs!=null) {
    		while(rs.next()){
    			answers.add(new Answers(rs.getInt("id"),rs.getString("answer"),rs.getBoolean("isapproved"),rs.getString("user_id"),rs.getInt("question_id"),rs.getDate("posted")));
    		}
    	}
    	
    	if(stm!=null){
    		stm.close();
		}
		if(rs!=null){
			rs.close();
		}
		if(con!=null){
			con.close();
		}
    	return answers;	 
   }
    
   /**
    * ...Method to approve a answer and making the question close 
    * to which this answer relates
    * @param id
    * @return true if successful approval
    */
   public boolean approveAnswer(int id) throws SQLException {
	   Connection con= ConnectionFactory.getInstance();
	   /*
	    * SQL query updating the answer making that answer approved
	    */
	   String sql = "UPDATE answers SET isapproved = 1 where id="+id;
	   int result = 0;
	   
	   Statement stm = con.createStatement();
	   result=stm.executeUpdate(sql);
   
	   	if(stm!=null){
			stm.close();
		}
		if(con!=null){
			con.close();
		}
	   
	   
	   if(result > 0) {
		   return true;
	   }	   
	   return false;
   }
   
   /**
    * ...Method to insert a answer
    * @param answers
    * @return true if successful insertion
    */
   public boolean insertAnswer(Answers answers) throws SQLException {
	   
	   Connection con= ConnectionFactory.getInstance();
		/*
		 * SQL query for inserting the answer into table
		 */
		String sql = "insert into answers(answer,isapproved,question_id,user_id,posted) values(?,?,?,?,?)";
		PreparedStatement ps = null;
		ps = con.prepareStatement(sql);
		ps.setString(1,answers.getAnswer());
		ps.setBoolean(2,answers.isApproved());
		ps.setInt(3,answers.getQuestion_id());
		ps.setString(4,answers.getUser_id());
		ps.setDate(5,answers.getPosted());
		int result = ps.executeUpdate();  //executing the query
		
		
    	if(ps!=null){
    		ps.close();
		}

		if(con!=null){
			con.close();
		}
		
		
		if(result==1){
			return true;
		}
		
		return false; 
   }
   
   /**
    * ...Method to delete a answer 
    * @param id
    * @return true if successful deletion
    */
   public boolean DeleteById(int id) throws SQLException {
	   
	   Connection con= ConnectionFactory.getInstance();
		Statement stmt=null;
	   	int result=0;
	   	ResultSet rs = null;
 	
 		stmt = con.createStatement();
 		String query = "select * from answers where id="+id;  	 // query for extracting that answer values
 		rs = stmt.executeQuery(query);
 		rs.first();
 		/*
 		 * if the selected answer is approved for the question
 		 * then open the question for new answers
 		 */
 		if(rs.getBoolean("isapproved")) {
 			query = "update questions set isclosed = 0 where id="+rs.getInt("question_id");
 			result = stmt.executeUpdate(query);
 		}
	   	String sql = "Delete from answers where id="+id;   		//Query for deleting the answer
		result = stmt.executeUpdate(sql);
		
		
	   	if(stmt!=null){
	   		stmt.close();
		}
	   	if(rs!=null) {
	   		rs.close();
	   	}
	   	
		if(con!=null){
			con.close();
		}
		
	   	if(result > 0) {
	   		return true;
	   	}
	    return false;
   }  
}